﻿using MSharp.Data.SPI;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MSharp.Data.DatabaseInfo
{
    public class PostgreSqlDBInfo : IDBInfo
    {
        private DB Db { get; set; }

        public PostgreSqlDBInfo(DB db)
        {
            this.Db = db;
            Refresh();
        }

        public string DBName
        {
            get { return (Db.ConnectionStringBuilder as Npgsql.NpgsqlConnectionStringBuilder).Database; }
        }

        public NameValueCollection TableComments { get; private set; }
        public List<string> TableNames { get; private set; }
        public Dictionary<string, TableInfo> TableInfoDict { get; private set; }
        public Dictionary<string, List<string>> TableColumnNameDict { get; private set; }
        public Dictionary<string, List<ColumnInfo>> TableColumnInfoDict { get; private set; }

        public Dictionary<string, NameValueCollection> TableColumnComments { get; private set; }

        private Dictionary<string, ColumnInfo> DictColumnInfo { get; set; }

        /// <summary>
        /// 表名 对应自增的 列名
        /// </summary>
        private Dictionary<string, string> Dict_Table_Identity_Column { get; set; }

        public List<string> DBNames { get; set; }

        public ColumnInfo this[string tableName, string columnName]
        {
            get
            {
                ColumnInfo colInfo;
                var strKey = (tableName + "@" + columnName).ToLower();
                DictColumnInfo.TryGetValue(strKey, out colInfo);
                return colInfo;
            }
        }

        public List<string> this[string tableName]
        {
            get
            {
                List<string> colNames;
                TableColumnNameDict.TryGetValue(tableName, out colNames);
                return colNames;
            }
        }

       

        public bool Refresh()
        {
            try
            {
                string dbSql = "select datname from pg_catalog.pg_database order by datname asc";
                DBNames = Db.ReadList<string>(dbSql);

                //http://blog.csdn.net/cicon/article/details/51577655

                string strSql = "select a.relname as name , b.description as value from pg_class a left join  (select * from pg_description where objsubid = 0 ) b on a.oid = b.objoid where a.relname in (select tablename from pg_tables where schemaname = 'public') order by a.relname asc";
               
                this.TableComments = Db.ReadNameValues(strSql);

                if (TableComments != null && TableComments.Count > 0)
                {
                    this.TableNames = TableComments.AllKeys.ToList();

                    this.TableInfoDict = new Dictionary<string, TableInfo>();
                    this.TableColumnNameDict = new Dictionary<string, List<string>>();
                    this.TableColumnInfoDict = new Dictionary<string, List<ColumnInfo>>();
                    this.TableColumnComments = new Dictionary<string, NameValueCollection>();
                    this.Dict_Table_Identity_Column = new Dictionary<string, string>();

                    this.DictColumnInfo = new Dictionary<string, ColumnInfo>();
                    foreach (var tableName in TableNames)
                    {
                        TableInfo tabInfo = new TableInfo();
                        tabInfo.TableName = tableName;
                        tabInfo.TabComment = TableComments[tableName];

                        strSql = @"select ordinal_position as Colorder,column_name as ColumnName,data_type as TypeName,
coalesce(character_maximum_length,numeric_precision,-1) as Length,numeric_scale as Scale,
case is_nullable when 'NO' then 0 else 1 end as CanNull,column_default as DefaultVal,
case  when position('nextval' in column_default)>0 then 1 else 0 end as IsIdentity, 
case when b.pk_name is null then 0 else 1 end as IsPK,c.DeText
from information_schema.columns 
left join (
	select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint  
	inner join pg_class on pg_constraint.conrelid = pg_class.oid 
	inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and  pg_attr.attnum = pg_constraint.conkey[1] 
	inner join pg_type on pg_type.oid = pg_attr.atttypid
	where pg_class.relname =:tableName and pg_constraint.contype='p' 
) b on b.colname = information_schema.columns.column_name
left join (
	select attname,description as DeText from pg_class
	left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
	left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
	where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname=:tableName
)c on c.attname = information_schema.columns.column_name
where table_schema='public' and table_name=:tableName order by ordinal_position asc";


                        tabInfo.Colnumns = Db.QueryTable(strSql, new { tableName = tableName.ToLower() }).ConvertToListObject<ColumnInfo>();


                        List<string> lstColName = new List<string>();
                        NameValueCollection nvcColDeText = new NameValueCollection();
                        foreach (ColumnInfo colInfo in tabInfo.Colnumns)
                        {
                            lstColName.Add(colInfo.ColumnName);
                            nvcColDeText.Add(colInfo.ColumnName, colInfo.DeText);

                            var strKey = (tableName + "@" + colInfo.ColumnName).ToLower();
                            DictColumnInfo.Add(strKey, colInfo);
                            if (colInfo.IsIdentity)
                            {
                                Dict_Table_Identity_Column[tableName] = colInfo.ColumnName;
                            }

                            if (colInfo.IsPK)
                            {
                                tabInfo.PriKeyColName = colInfo.ColumnName;
                                if (colInfo.IsIdentity)
                                {
                                    tabInfo.PriKeyType = PrimaryKeyType.AUTO;
                                }
                                else
                                {
                                    tabInfo.PriKeyType = PrimaryKeyType.SET;
                                }
                            }
                        }

                        TableInfoDict.Add(tableName, tabInfo);
                        TableColumnNameDict.Add(tableName, lstColName);
                        TableColumnInfoDict.Add(tableName, tabInfo.Colnumns);
                        TableColumnComments.Add(tableName, nvcColDeText);
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 查询当前表 的自增 列名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>序列名称</returns>
        public string IdentityColumnName(string tableName)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            string colName;
            if (Dict_Table_Identity_Column.TryGetValue(tableName, out colName))
            {
                return colName;
            }
            return string.Empty;
        }

        public bool IsExistTable(string tableName)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            return TableNames.Contains(tableName);
        }

        public bool IsExistColumn(string tableName, string columnName)
        {
            var strKey = (tableName + "@" + columnName).ToLower();
            return DictColumnInfo.ContainsKey(strKey);
        }


        public Dictionary<string, DateTime> GetTableStruct_Modify()
        {
            throw new Exception("暂未提供查询PostgreSql表结构修改时间的功能！");
        }

        public string GetColumnComment(string tableName, string columnName)
        {
            ColumnInfo colInfo = null;
            var strKey = (tableName + "@" + columnName).ToLower();
            DictColumnInfo.TryGetValue(strKey, out colInfo);
            return colInfo?.DeText;
        }


        public string GetTableComment(string tableName)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            return TableComments[tableName];
        }

        public List<ColumnInfo> GetColumns(string tableName)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            List<ColumnInfo> colInfos = null;
            TableColumnInfoDict.TryGetValue(tableName, out colInfos);
            return colInfos;
        }


        public bool SetTableComment(string tableName, string comment)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            if (!TableNames.Contains(tableName))
            {
                //throw new ArgumentException("该表不存在！" + tableName, "tableName");
                return false;
            }

            string upsert_sql = string.Empty;
            comment = (comment ?? string.Empty).Replace("'", "");
            try
            {
                upsert_sql = @"comment on table " + tableName + " is '" + comment + "'";
                Db.ExecSql(upsert_sql);

                TableComments[tableName] = comment;

                var tabInfo = TableInfoDict[tableName];
                tabInfo.TabComment = comment;
                TableInfoDict[tableName] = tabInfo;
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }


        public bool SetColumnComment(string tableName, string columnName, string comment)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            columnName = (columnName ?? string.Empty).ToLower();

            if (!TableNames.Contains(tableName))
            {
                //throw new ArgumentException("该表不存在！" + tableName, "tableName");
                return false;
            }

            if (!DictColumnInfo.ContainsKey(tableName + "@" + columnName))
            {
                //throw new ArgumentException(tableName + "表不存在" + columnName + "列！", "columnName");
                return false;
            }

            string upsert_sql = string.Empty;
            comment = (comment ?? string.Empty).Replace("'", "");
            try
            {
                upsert_sql = @"comment on column " + tableName + "." + columnName + " is '" + comment + "'";
                Db.ExecSql(upsert_sql);

                List<ColumnInfo> lstColInfo = TableColumnInfoDict[tableName];

                NameValueCollection nvcColDesc = new NameValueCollection();
                lstColInfo.ForEach(t =>
                {
                    if (t.ColumnName.Equals(columnName))
                    {
                        t.DeText = comment;
                    }
                    nvcColDesc.Add(t.ColumnName, t.DeText);
                });

                TableColumnInfoDict.Remove(tableName);
                TableColumnInfoDict.Add(tableName, lstColInfo);

                TableColumnComments.Remove(tableName);
                TableColumnComments.Add(tableName, nvcColDesc);

                var strKey = (tableName + "@" + columnName);
                ColumnInfo colInfo = DictColumnInfo[strKey];
                colInfo.DeText = comment;
                DictColumnInfo[strKey] = colInfo;
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool DropTable(string tableName)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            if (!TableNames.Contains(tableName))
            {
                throw new ArgumentException("该表不存在！" + tableName, "tableName");
            }

            string drop_sql = string.Empty;
            try
            {

                drop_sql = "drop table " + tableName;
                Db.ExecSql(drop_sql);

                this.TableComments.Remove(tableName);

                this.TableNames = TableComments.AllKeys.ToList();

                this.TableInfoDict.Remove(tableName);
                this.TableColumnInfoDict.Remove(tableName);
                this.TableColumnComments.Remove(tableName);

                var lstColName = TableColumnNameDict[tableName];

                foreach (var colName in lstColName)
                {
                    var strKey = (tableName + "@" + colName).ToLower();
                    this.DictColumnInfo.Remove(strKey);
                }

                this.TableColumnNameDict.Remove(tableName);

            }
            catch (Exception ex)
            {

                return false;
            }
            return true;
        }


        public bool DropColumn(string tableName, string columnName)
        {
            tableName = (tableName ?? string.Empty).ToLower();
            columnName = (columnName ?? string.Empty).ToLower();

            if (!TableNames.Contains(tableName))
            {
                throw new ArgumentException("该表不存在！" + tableName, "tableName");
            }

            var strKey = (tableName + "@" + columnName).ToLower();

            if (!DictColumnInfo.ContainsKey(strKey))
            {
                throw new ArgumentException(tableName + "表不存在" + columnName + "列！", "columnName");
            }

            try
            {
                string drop_sql = "alter table {0} drop column {1}";
                drop_sql = string.Format(drop_sql, tableName, columnName);
                Db.ExecSql(drop_sql);

                this.DictColumnInfo.Remove(strKey);

                var nvc = TableColumnComments[tableName];
                nvc.Remove(columnName);
                TableColumnNameDict[tableName] = nvc.AllKeys.ToList();

                var lstColInfo = TableColumnInfoDict[tableName];
                ColumnInfo curColInfo = null;
                lstColInfo.ForEach(t =>
                {
                    if (t.ColumnName.Equals(columnName))
                    {
                        curColInfo = t;

                        //tabInfo 对应的 主键类型和主键列 也需要 跟着修改。
                        if (curColInfo.IsPK)
                        {
                            var tabInfo = TableInfoDict[tableName];
                            tabInfo.PriKeyType = PrimaryKeyType.UNKNOWN;
                            tabInfo.PriKeyColName = null;
                            TableInfoDict[tableName] = tabInfo;
                        }
                        return;
                    }
                });
                lstColInfo.Remove(curColInfo);
                TableColumnInfoDict[tableName] = lstColInfo;

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
    }
}
